﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;

namespace DBManager.UserCode
{
    public class SqlCommon
    {
        public static string GetConnStr(string serverName, string userName, string userPwd, string dbName = "master")
        {
            return String.Format("Data Source={0};Initial Catalog = {1};User ID = {2};PWD = {3}", serverName, dbName, userName, userPwd);
        }

        /// <summary>
        /// 数据库连接是否成功
        /// </summary>
        /// <returns></returns>
        public static bool ConnectTest(string connStr)
        {
            bool result = false;
            //创建连接对象
            SqlConnection conn = new SqlConnection(connStr);
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                    result = true;
            }
            catch
            {
                MessageBox.Show("数据库连接失败，请检查用户名及密码！");
                return result;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        /// <summary>
        /// 获取服务器所有数据库名
        /// </summary>
        public static DataTable GetDataBase(string connStr)
        {
            DataTable DBNameTable = new DataTable();
            if (SqlCommon.ConnectTest(connStr))
            {
                SqlConnection Connection = new SqlConnection(connStr);
                SqlDataAdapter Adapter = new SqlDataAdapter("select name from master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb','ReportServer','ReportServerTempDB')", Connection);
                lock (Adapter)
                {
                    Adapter.Fill(DBNameTable);
                }
            }
            return DBNameTable;
        }

        /// <summary>
        /// 获取指定数据库所有表名
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string connStr)
        {
            DataTable dtNames = new DataTable();
            if (SqlCommon.ConnectTest(connStr))
            {
                SqlConnection Connection = new SqlConnection(connStr);
                SqlDataAdapter Adapter = new SqlDataAdapter("select name from sys.tables order by name", Connection);

                lock (Adapter)
                {
                    Adapter.Fill(dtNames);
                }
            }
            return dtNames;
        }
        /// <summary>
        /// 获取指定连接下指定数据库的备份文件
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public static DataTable GetBackupList(string connStr,string dbName)
        {
            DataTable backupNames = new DataTable();
            if (SqlCommon.ConnectTest(connStr))
            {
                StringBuilder sb = new StringBuilder();
                sb.Append(" IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp");
                sb.Append(" CREATE TABLE #Temp(directory nvarchar(200),depth int,IsFile bit)");
                //depth参数，查找层数，为0时，查找所有层。file为1时查找文件
                sb.Append(" INSERT #Temp EXEC master.dbo.xp_dirtree @path = '" + FileCommon.backupPath + "',@depth = 1,@file = 1");
                sb.Append(" SELECT directory name FROM #Temp WHERE IsFile=1 AND directory like '%_" + dbName + ".bak' ORDER BY directory DESC");
                SqlConnection Connection = new SqlConnection(connStr);
                SqlDataAdapter Adapter = new SqlDataAdapter(sb.ToString(), Connection);

                lock (Adapter)
                {
                    Adapter.Fill(backupNames);
                }
            }
            return backupNames;
        }
    }
}
